package com.gomefinance.excelutils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Excel2003Reader {
	private POIFSFileSystem fileSystem;
	private HSSFWorkbook workbook;
	private HSSFSheet sheet;
	private HSSFRow row;
	private int colmunNum;// 总共有多少列，以标题为准
	private int rowNum;
	private int curRow;// 当前行
	private int firstRow;//第几行还是解析-下标

	private IRowReader rowReader;

	private  IRowWrite rowWrite;
	
	public int getFirstRow() {
		return firstRow;
	}

	public void setFirstRow(int firstRow) {
		this.firstRow = firstRow;
	}

	public IRowWrite getRowWrite() {
		return rowWrite;
	}

	public void setRowWrite(IRowWrite rowWrite) {
		this.rowWrite = rowWrite;
	}

	public IRowReader getRowReader() {
		return rowReader;
	}

	public void setRowReader(IRowReader rowReader) {
		this.rowReader = rowReader;
	}

	/**
	 * 遍历excel下所有的sheet
	 * 
	 * @throws Exception
	 */
	public void process(InputStream in) throws Exception {
		try {
			fileSystem = new POIFSFileSystem(in);
			workbook = new HSSFWorkbook(fileSystem);

			// 获得总页签数
			int sheetsNum = workbook.getNumberOfSheets();
			for (int i = 0; i < sheetsNum; i++) {
				sheet = workbook.getSheetAt(i);
				// 读取excel内容
				readExcelContent(sheet, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			in.close();
		}
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public void readExcelContent(HSSFSheet sheet, int sheetIndex) throws Exception {
		List<String> content = new ArrayList<String>();
		// 得到总行数
		rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		colmunNum = row.getPhysicalNumberOfCells();

		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = firstRow; i <= rowNum; i++) {
			curRow = i-firstRow+1;
			row = sheet.getRow(i);
			for (int j = 0; j < colmunNum; j++) {
				// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
				// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
				// str += getStringCellValue(row.getCell((short) j)).trim() +
				// "-";
				content.add(getCellFormatValue(row.getCell((short) j)).trim());
			}
			rowReader.getRows(sheetIndex, curRow, content, i == rowNum);
			content.clear();
		}
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式

					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();

					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:SS");
					cellvalue = sdf.format(date);

				} else {// 如果是纯数字
					// 取得当前Cell的数值
					DecimalFormat df = new DecimalFormat("##.##");
					cellvalue = df.format((cell.getNumericCellValue()));
				}
				break;
			}
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				cellvalue = "0";
				break;
			// 默认的Cell值
			default:
				cellvalue = "0";
			}
		} else {
			cellvalue = "0";
		}
		return cellvalue;
	}

	/**
	 * 创建EXCEL处理
	 * @throws Exception
	 */
	public void writeProcess(String filePath) throws Exception {
		// 写数据
		rowWrite.setRows(filePath);

	}
}
